Do SQL Plan Baselines Use Hints? – Take 2
Warning – This post is almost pure speculation!
You may have read a previous post where I said I thought Baselines didn’t use hints. We’ll I’ve changed my mind. I no longer think that Baselines store an actual plan for a SQL statement. (See my previous post – Do SQL Plan Baselines Use Hints?) I never could find a place where the plan data was stored (only the plan_hash_value). So I had a closer look at some more 10053 trace files. Here’s a quick run down of what I found. First the set up:
SQL> @flush_pool
System altered.
SQL> select avg(pk_col) from kso.little_skew where col1 = 1;
AVG(PK_COL)
-----------
310999.357
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
9df852xw86wxt 0 3498336203 1 .18 3,437 select avg(pk_col) from kso.little_skew where col1 = 1
SQL> @dplan
Enter value for sql_id: 9df852xw86wxt
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9df852xw86wxt, child number 0
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 = 1
Plan hash value: 3498336203
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 907 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| LITTLE_SKEW | 620K| 5449K| 907 (2)| 00:00:11 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1)
19 rows selected.
SQL> select /*+ index (a little_skew_col1) */ avg(pk_col) from kso.little_skew a where col1 = 1;
AVG(PK_COL)
-----------
310999.357
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
9df852xw86wxt 0 3498336203 1 .18 3,437 select avg(pk_col) from kso.little_skew where col1 = 1
fbnx1bws6n4xu 0 2709260180 1 .86 5,278 select /*+ index (a little_skew_col1) */ avg(pk_col) from ks
o.little_skew a where col1 = 1
SQL> @dplan
Enter value for sql_id: fbnx1bws6n4xu
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fbnx1bws6n4xu, child number 0
-------------------------------------
select /*+ index (a little_skew_col1) */ avg(pk_col) from
kso.little_skew a where col1 = 1
Plan hash value: 2709260180
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 20835 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| LITTLE_SKEW | 620K| 5449K| 20835 (1)| 00:04:11 |
|* 3 | INDEX RANGE SCAN | LITTLE_SKEW_COL1 | 620K| | 3213 (1)| 00:00:39 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=1)
21 rows selected.
SQL> -- need to create baseline for original statement to get SQL_HANDLE
SQL>
SQL> !cat create_baseline.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
sql_id=>'&sql_id', -
plan_hash_value=>&plan_hash_value,-
fixed=>'&fixed');
SQL> @create_baseline
Enter value for sql_id: 9df852xw86wxt
Enter value for plan_hash_value: 3498336203
Enter value for fixed: NO
PL/SQL procedure successfully completed.
SQL> -- now add the other plan as a fixed Baseline
SQL>
SQL> !cat create_baseline2.sql
var ret number
exec :ret := dbms_spm.load_plans_from_cursor_cache(-
sql_id=>'&sql_id', -
plan_hash_value=>&plan_hash_value,-
sql_handle=>'&sql_handle',-
fixed=>'&fixed');
SQL> @create_baseline2
Enter value for sql_id: fbnx1bws6n4xu
Enter value for plan_hash_value: 2709260180
Enter value for sql_handle: SYS_SQL_19fcc3effcce06c0
Enter value for fixed: YES
PL/SQL procedure successfully completed.
SQL> @baselines
Enter value for sql_text:
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FIX LAST_EXECUTED
------------------------------ ------------------------------ -------------------------------------------------- ------- --- --- ----------------
SYS_SQL_19fcc3effcce06c0 SYS_SQL_PLAN_fcce06c08b1420d2 select avg(pk_col) from kso.little_skew where col1 YES YES NO
SYS_SQL_19fcc3effcce06c0 SYS_SQL_PLAN_fcce06c02fdfd157 select avg(pk_col) from kso.little_skew where col1 YES YES YES
SQL> select avg(pk_col) from kso.little_skew where col1 = 1;
AVG(PK_COL)
-----------
310999.357
SQL> /
AVG(PK_COL)
-----------
310999.357
SQL> -- check to see if it's using the fixed Baseline
SQL> @find_sql
Enter value for sql_text: %little%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ------ ---------- ---------- ------------- ------------ ------------------------------------------------------------
9df852xw86wxt 0 3498336203 2 .19 3,279 select avg(pk_col) from kso.little_skew where col1 = 1
fbnx1bws6n4xu 0 2709260180 1 .86 5,278 select /*+ index (a little_skew_col1) */ avg(pk_col) from ks
o.little_skew a where col1 = 1
SQL>
SQL> @dplan
Enter value for sql_id: 9df852xw86wxt
Enter value for child_no: 0
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9df852xw86wxt, child number 0
-------------------------------------
select avg(pk_col) from kso.little_skew where col1 = 1
Plan hash value: 3498336203
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 907 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | TABLE ACCESS FULL| LITTLE_SKEW | 620K| 5449K| 907 (2)| 00:00:11 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=1)
19 rows selected.
SQL> -- no it's not using the Baseline, why not?
So we created a Fixed Baseline on a statement that used a hint to force an index based access path, attached it to another statement with no hints, and then re-ran the un-hinted statement. It did not pick up the Fixed Baseline. The next step was to have a look at a 10053 trace file. Here are some excerpts from the 10053 trace file.
...
SPM: statement found in SMB
...
******************************************
----- Current SQL Statement for this session (sql_id=9df852xw86wxt) -----
select avg(pk_col) from kso.little_skew where col1 = 1
*******************************************
...
SPM: cost-based plan found in the plan baseline, planId = 2333352146
SPM: fixed planId's of plan baseline are: 803197271
SPM: using qksan to reproduce, cost and select accepted plan, sig = 1872586980585244352
SPM: reproduce plan round 1, using session OFE=11010007
SPM: using qksan to reproduce accepted plan, planId = 803197271
Registered qb: SEL$1 0xf6d872ec (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 objn=90376 hint_alias="LITTLE_SKEW"@"SEL$1"
DOP: Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=7039h2744jjrv) -----
/* SQL Analyze(110,0) */ select avg(pk_col) from kso.little_skew where col1 = 1
*******************************************
...
SPM: planId in plan baseline = 803197271, planId of reproduced plan = 2333352146
------- START SPM Plan Dump -------
SPM: failed to reproduce the plan using the following info:
parse_schema name : SYS
plan_baseline signature : 1872586980585244352
plan_baseline plan_id : 803197271
plan_baseline hintset :
hint num 1 len 27 text: IGNORE_OPTIM_EMBEDDED_HINTS
hint num 2 len 37 text: OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
hint num 3 len 22 text: DB_VERSION('11.1.0.7')
hint num 4 len 8 text: ALL_ROWS
hint num 5 len 22 text: OUTLINE_LEAF(@"SEL$1")
hint num 6 len 57 text: INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("LITTLE_SKEW"."COL1"))
SPM: generated non-matching plan:
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
-----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 907 | |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | TABLE ACCESS FULL | LITTLE_SKEW| 605K | 5449K | 907 | 00:00:11 |
-----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("COL1"=1)
Content of other_xml column
===========================
db_version : 11.1.0.7
parse_schema : SYS
plan_hash : 3498336203
plan_hash_2 : 2333352146
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "LITTLE_SKEW"@"SEL$1")
END_OUTLINE_DATA
*/
------- END SPM Plan Dump -------
So it appears that the Baseline is found in the SMB (SQL Management Base). It then appears to try to reproduce the plan in a separate session using the hints. It then announces that it came up with a different plan and spits out the plan data that it came up with. So it appears that the plan_hash_value is just used for comparison to see if it came up with the same plan as the one that was in place when the Baseline was generated. If not, then it falls back to the regular (no Baseline) behavior. Not at all what I had hoped for!
It may be that 11gR1 is an intermediate step and that a future version (11gR2 maybe, I hope) will actually store the plan details instead of just the plan hash value. But as I said in the intro, this is complete speculation on my part.
Interesting. It appears there can be an SMB “statement match”, but an attempt to “reproduce accepted plan” failed with “SPM: generated non-matching plan”. The important bit for me was seeing the 10053 and two types of “matches”, e.g. statement match and matching/non-matching plan.
In the documentation, it says “non-match” might happen if you remove an index from all accepted plans. E.g. “if a change in the system (such as a dropped index) causes all accepted plans to become non-reproducible, the optimizer will select the best-cost plan”. I think you created the opposite case with same effect using a hint, e.g. forcing an index with hint. Another test case might limit using a variable from the plan history such as bind variables, etc. E.g. “plan history will include relevant information used by the optimizer to reproduce an execution plan, such as the SQL text, outline, bind variables, and compilation environment”.
Good luck.
You say ‘I never could find a place where the plan data was stored (only the plan_hash_value).’ Where did you find it?
I can see the plan_hash_value in the output from dbms_xplan.display_sql_plan_baseline but view dba_sql_plan_baselines does not have a plan_hash_value. It has a plan_name but it does not look anything like a plan_hash_value and I do not know where any cross-reference is.
Thanks,
Mike
Mike,
DBA_SQL_PLAN_BALSELINES is based in part on sqlobj$ which has a column (not exposed in DBA_SQL_PLAN_BASELINES) called plan_id. It does not match the actual plan_hash_value column from v$sql but I believe it is basically the same thing but possibly with additional columns from the v$sql_plan table included the hash calculation. I have not found any cross reference unfortunately. If you Wolfgang a statement that uses a Baseline you will see a line in the trace file something like this:
planId in plan baseline = 2333352146, planId of reproduced plan = 2333352146
This line let’s you know whether the plan reproduced using the Baseline hints matched or whether it fell back to re-optimizing the plan from scratch.
Here’s the definition of the DBA_SQL_PLAN_BASELINES view and a describe on sqlobj$:
SQL> @view_def Enter value for view_name: DBA_SQL_PLAN_BASELINES VIEW_NAME TEXT ------------------------------ -------------------------------------------------------------------------------- DBA_SQL_PLAN_BASELINES SELECT /*+ dynamic_sampling(3) */ so.signature, st.sql_handle, st.sql_text, so.name, ad.creator, DECODE(ad.origin, 1, 'MANUAL-LOAD', 2, 'AUTO-CAPTURE', 3, 'MANUAL-SQLTUNE', 4, 'AUTO-SQLTUNE', 'UNKNOWN'), ad.parsing_schema_name, ad.description, ad.version, ad.created, ad.last_modified, so.last_executed, ad.last_verified, DECODE(BITAND(so.flags, 1), 1, 'YES', 'NO'), DECODE(BITAND(so.flags, 2), 2, 'YES', 'NO'), DECODE(BITAND(so.flags, 4), 4, 'YES', 'NO'), DECODE(BITAND(so.flags, 8), 8, 'YES', 'NO'), ad.optimizer_cost, ad.module, ad.action, ad.executions, ad.elapsed_time, ad.cpu_time, ad.buffer_gets, ad.disk_reads, ad.direct_writes, ad.rows_processed, ad.fetches, ad.end_of_fetch_count FROM sqlobj$ so, sqlobj$auxdata ad, sql$text st WHERE so.signature = st.signature AND ad.signature = st.signature AND so.signature = ad.signature AND so.plan_id = ad.plan_id AND so.obj_type = 2 AND ad.obj_type = 2 SQL> desc sqlobj$ Name Null? Type -------------------------------------------------------------------------------------- -------- ---------------------------------------------------------- SIGNATURE NOT NULL NUMBER CATEGORY NOT NULL VARCHAR2(30) OBJ_TYPE NOT NULL NUMBER PLAN_ID NOT NULL NUMBER NAME NOT NULL VARCHAR2(30) FLAGS NOT NULL NUMBER LAST_EXECUTED TIMESTAMP(6) SPARE1 NUMBER SPARE2 CLOBHope that helps.
Kerry
Hello,
sorry for beeing so late. plan_id in sys.sqlobj$ is plan_hash_2 (s. in other_xml for ex.). Baseline hints one can find in other_xml from sys.sqlobj$plan.
For ex. so:
select other_xml into v_xml from sys.sqlobj$plan where (signature,category,obj_type,plan_id) in (select signature,category,obj_type,plan_id from sys.sqlobj$ where name=’&plan_name’ and obj_type=2) and other_xml is not null;
Best regards
Leonid
We have also noticed cases in which fixed plans are not being used. In each case the scenario was identical to your test case. A baseline was created by auto-capturing or manually inserting a plan in a baseline. A hinted statement was then run to create the desired plan and that was added to the baseline as the fixed plan. In each case where we had problems, we found that we had aliased a table in the hinted statement (as you did in the example above) and had not had a corresponding alias in the original statement. Removing the alias from the hinted plan solved the problem.
In your test case above removing the alias and using the table name in the hint seems to allow the unhinted statement to use the fixed baseline plan.
Larry Thomas
Thanks for the comment Larry.
You’re exactly correct. It all comes down to whether a hint can be used or not. The format of many of the hints specify an object in a query block like the INDEX hint in our example above:
INDEX_RS_ASC(@”SEL$1″ “A”@”SEL$1” (“LITTLE_SKEW”.”COL1″)
As you pointed out, if you use an alias (in this case we used A), then the original statement can apply that hint because it doesn’t have an object (“A”) in the specified query block (“SEL$1”).
Kerry
[…] Osborne 1. Do SQL Plan Baselines use hints (pt.1) 2. Do SQL Plan Baselines use hints (pt. 2) 3. SQL Plan Management – SQL Plan Baselines 4. Why isn’t Oracle using my outline/ SQL […]
[…] Plan Baselines. The comments in the blogs also explained why alias plays a role in the stored SPB http://kerryosborne.oracle-guy.com/2009/04/do-sql-plan-baselines-use-hints-take-2/ SPM walks through http://www.oracle-base.com/articles/11g/SqlPlanManagement_11gR1.php […]
Thanks for the detailed information. What are the major differences between SQL profile and SQL baselines?
Hi Raja,
Major differences:
1. Profiles allow you to add any set of hints you want to a statement via dbms_sqltune.import_sql_profile (search this blog and you’ll see many examples). Baselines do not have such a feature.
2. Profiles have a force_matching flag that allows them to be applied to more than one statement (i.e. to statements that use literals instead of bind variables). Baselines don’t have that feature.
3. Baselines know what plan they are trying to reproduce (they store a plan hash value). If the set of hints stored by the baseline do not cause the optimizer to pick the original plan, the optimizer tries again with another baseline or without any hints at all. Profiles blindly apply the hints and have no knowledge of the plan they are trying to get.
I did a presentation at the Hotsos Symposium a couple of years ago with a lot more details.
Here’s a link to the slides: Controlling Execution Plans